package com.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.concurrent.locks.ReentrantLock;

import com.tools.Constants;
import com.tools.ServerUtils;

/**
 * @author lemonjesnme
 * @category is the DB operation.
 */
public class DB {

	private static Connection connection;
	private static Statement statement;
	private static ReentrantLock rl;
	public static boolean connDB() {
		String driverName = "com.mysql.jdbc.Driver";
		String username = Constants.USER;
		String password = Constants.PASSWORD;
		String dbName = Constants.DBNAME;
		String serverAddress = Constants.SERVER_ADDRESS;
		String url = "jdbc:mysql://" + serverAddress + "/" + Constants.DBNAME
				+ "?useUnicode=true&characterEncoding=UTF-8";
		try {
			Class.forName(driverName);
			System.out.println("[DB] url:" + url + " username:" + username
					+ " password:" + password);
			connection = java.sql.DriverManager.getConnection(url, username,
					password);
			statement = connection.createStatement();
			try {
				if (statement != null) {
					statement.execute("create database if not exists "
							+ Constants.DBNAME + " default charset=utf8");
				} else {
					ServerUtils.sleep(2000);
				}
			} catch (Exception e3) {
				System.out.println("[DB]  create database failed!");
			}
			System.out.println("[db] DB:" + dbName + " connect success!");
		} catch (SQLException e) {
			System.out.println("[db] connect DB " + dbName + " sql error.");
			return false;
		} catch (Exception e) {
			System.out.println("[db] connect DB " + dbName + " other error.");
			return false;
		}
		return true;
	}

	public static boolean closeDB() {
		try {
			statement.close();
			connection.close();
		} catch (Exception e) {
			System.out.println("[db] close DB error.");
			return false;
		}
		return true;
	}

	public static ResultSet select(String sql) {
		ResultSet rs = null;
		try {
			System.out.println("[db] " + sql.toString());
			rs = statement.executeQuery(sql.toString());
		} catch (SQLException e) {
			System.out.println("[db] select error.");
			return null;
		}
		return rs;
	}

	@SuppressWarnings("unchecked")
	private static String getTableName(Class clz) {
		return clz.getSimpleName();
	}

	@SuppressWarnings("unchecked")
	public static boolean createTable(Class clz) {
		Field[] fields = null;
		try {
			fields = clz.getFields();
		} catch (SecurityException e) {
			e.printStackTrace();
		}
		StringBuffer sql = new StringBuffer();
		sql.append("create table if not exists ");
		sql.append(getTableName(clz));
		sql.append("(");
		for (int i = 0; i < fields.length; i++) {
			Field fd = fields[i];
			sql.append(fd.getName());
			sql.append(" TEXT");
			sql.append(",");
		}
		sql.setCharAt(sql.length() - 1, ')');
		sql.append(" DEFAULT CHARSET=utf8");
		sql.append(";");
		System.out.println(sql.toString());
		try {
			statement.execute(sql.toString());
		} catch (SQLException e) {
			System.out.println("[db] createTable " + getTableName(clz)
					+ " error.");
			return false;
		}
		return true;
	}

	public static ResultSet select(String tableName, String key,
			String whereKey, String whereValue, String orderBy) {
		StringBuffer sql = new StringBuffer();
		if (tableName != null) {
			sql.append("select ");
			if (key != null)
				sql.append(key);
			else
				sql.append("*");
			sql.append(" from ");
			sql.append(tableName);
			if ((whereKey != null) && (whereValue != null)) {
				sql.append(" where ");
				sql.append(whereKey);
				sql.append(" = ");
				sql.append(whereValue);
			}
			if (orderBy != null) {
				sql.append(" order by ");
				sql.append(orderBy);
				sql.append(" desc");
			}
			sql.append(";");
		}
		ResultSet rs = null;
		try {
			System.out.println(sql.toString());
			rs = statement.executeQuery(sql.toString());
		} catch (SQLException e) {
			System.out.println("[db] select " + tableName + " error.");
			return null;
		}
		return rs;
	}

	public static boolean insert(String tableName, String primaKey,
			String value, HashMap<String, String> hm) {
		if ((primaKey != null) && (value != null)) {
			String sql = "insert into " + tableName + "(" + primaKey
					+ ") values ('" + value + "');";
			try {
				statement.execute(sql);
			} catch (SQLException e) {
				System.out.println("[db] insert " + tableName + " primakey:"
						+ primaKey + " error.");
			}
		}
		if (hm == null) {
			return true;
		}
		Set<String> set = hm.keySet();
		Iterator<String> it = set.iterator();
		while (it.hasNext()) {
			String key = it.next();
			String sql1 = "update " + tableName + " set " + key + " = '"
					+ hm.get(key) + "' where " + primaKey + "=" + value + ";";
			System.out.println("[db] " + sql1);
			try {
				statement.execute(sql1);
			} catch (SQLException e) {
				e.printStackTrace();
				System.out.println("[db] update " + tableName + " key:" + key
						+ " error.");
				return false;
			}
		}
		return true;
	}

	public static boolean dropTable(String tableName) {
		StringBuffer sql = new StringBuffer();
		if (tableName != null) {
			sql.append("drop table ");
			sql.append(tableName);
			sql.append(";");
		}
		try {
			statement.execute(sql.toString());
		} catch (SQLException e) {
			System.out.println("[db] dropTable " + tableName + " error.");
			return false;
		}
		return true;
	}

	public static boolean deleteRow(String tableName, String key, String value) {
		if ((tableName == null) || (key == null) || (value == null)) {
			return false;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ");
		sql.append(tableName);
		sql.append(" where ");
		sql.append(key);
		sql.append("=");
		sql.append("'" + value + "';");
		try {
			if (tableName != null) {
				statement.execute(sql.toString());
				System.out.println(sql.toString());
			}
		} catch (SQLException e) {
			System.out.println("[db] delete " + tableName + "where " + key
					+ "=" + value + " error.");
		}
		return true;
	}

	public static boolean update(String sql) {
		try {
			System.out.println("[db] sql:" + sql);
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public static boolean insert(String sql) {
		try {
			System.out.println("[db] sql:" + sql);
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public static boolean delete(String sql) {
		try {
			System.out.println("[db] sql:" + sql);
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public static boolean exeCmd(String sql) {
		try {
			System.out.println("[db] sql:" + sql);
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
}
